IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'MD3.CompareSchemas') AND type IN (N'P', N'PC'))
DROP PROCEDURE MD3.CompareSchemas
GO


CREATE PROCEDURE MD3.CompareSchemas
	( 
		@NetNewDB SYSNAME,
		@UpgradedDB SYSNAME
    )
    
AS      
BEGIN

/*
	Can run on either @NetNewDB or @UpgradedDB

	Captures schema differences that can be output to a text file and examined later.  
	This is typically run as part of a CI build loop.

	It tests an upgraded db's schema against a net new db's schema for the same build.  
	or
	It tests one db's schema against another for schematic differences.
	or
	Run it on today's build (netnew) vs yesterday's build (upgraded) to determine "what has changed in this build"

	EXEC MD3.CompareSchemas 'TEST', 'AdventureWorks2012'

	We are mising quite a bit of functionality in this validator, but it is a good start and will generally find the most egregious issues for you.  

*/


	SET NOCOUNT ON    
	DECLARE @SQL NVARCHAR(4000)


SELECT 'Looking for Missing Schema Items between net new and upgraded'
SELECT @SQL = N'
(
SELECT name , ''Missing Object From ' + @NetNewDB + ''' Description ,type_desc as object_type from ' + @UpgradedDB + '.sys.objects WHERE type in (''FN'',''IF'',''F'',''U''/*,''UQ''*/,''D'',''P'',''PK'',''V'',''TR'',''TF'') and name not like ''%_[0-9][0-9][0-9][0-9][0-9]''
EXCEPT 
SELECT name , ''Missing Object From ' + @NetNewDB + ''' Description ,type_desc as object_type from ' + @NetNewDB + '.sys.objects WHERE type in (''FN'',''IF'',''F'',''U''/*,''UQ''*/,''D'',''P'',''PK'',''V'',''TR'',''TF'') and name not like ''%_[0-9][0-9][0-9][0-9][0-9]''
)  
UNION ALL
(
SELECT name , ''Missing Object From ' + @UpgradedDB+ ''' Description ,type_desc as object_type from ' + @NetNewDB + '.sys.objects WHERE type in (''FN'',''IF'',''F'',''U''/*,''UQ''*/,''D'',''P'',''PK'',''V'',''TR'',''TF'')  and name not like ''%_[0-9][0-9][0-9][0-9][0-9]''
EXCEPT 
SELECT name , ''Missing Object From ' + @UpgradedDB + ''' Description ,type_desc as object_type from ' + @UpgradedDB + '.sys.objects WHERE type in (''FN'',''IF'',''F'',''U''/*,''UQ''*/,''D'',''P'',''PK'',''V'',''TR'',''TF'')  and name not like ''%_[0-9][0-9][0-9][0-9][0-9]''
)
'
--SELECT @SQL
EXEC sp_executesql @SQL


SELECT 'Looking for Missing columns between net new and upgraded'
SELECT @SQL = N'
(
SELECT o.name as objectname,c.name as colname, ''Missing Column From ' + @NetNewDB + ''' from ' + @UpgradedDB + '.sys.columns c join ' + @UpgradedDB + '.sys.objects o on c.object_id = o.object_id and o.type = ''U'' and o.name not like ''%_[0-9][0-9][0-9][0-9][0-9]''
EXCEPT 
SELECT o.name as objectname,c.name as colname,''Missing Column From ' + @NetNewDB + ''' from ' + @NetNewDB + '.sys.columns c join ' + @NetNewDB + '.sys.objects o on c.object_id = o.object_id and o.type = ''U'' and o.name not like ''%_[0-9][0-9][0-9][0-9][0-9]''
)  
UNION ALL
(
SELECT o.name as objectname,c.name as colname, ''Missing Column From ' + @UpgradedDB+ ''' from ' + @NetNewDB + '.sys.columns c join ' + @NetNewDB + '.sys.objects o on c.object_id = o.object_id and o.type = ''U'' and o.name not like ''%_[0-9][0-9][0-9][0-9][0-9]''
EXCEPT 
SELECT o.name as objectname,c.name as colname,''Missing Column From ' + @UpgradedDB + ''' from ' + @UpgradedDB + '.sys.columns c join ' + @UpgradedDB + '.sys.objects o on c.object_id = o.object_id and o.type = ''U'' and o.name not like ''%_[0-9][0-9][0-9][0-9][0-9]''
)
'
--SELECT @SQL
EXEC sp_executesql @SQL


SELECT 'Looking for Index Discrepancies:'
SELECT @SQL = N'
(
select obj.name as TableName
	,ind.name as IndexName
	,ind.type_desc as Clustering
	,col.name as ColName
	,keys.keyno as ColPosition
	,''Missing From DB: ' + @UpgradedDB + ''' AS Descr
from ' + @NetNewDB + '.sys.objects obj
join ' + @NetNewDB + '.sys.indexes ind on obj.object_id = ind.object_id
join ' + @NetNewDB + '.sys.columns col on obj.object_id = col.object_id
join ' + @NetNewDB + '.sys.sysindexkeys keys on (keys.id = col.object_id AND keys.colid = col.column_id) AND (ind.object_id = keys.id AND ind.index_id = keys.indid)
EXCEPT 
select obj.name as TableName
	,ind.name as IndexName
	,ind.type_desc as Clustering
	,col.name as ColName
	,keys.keyno as ColPosition
	,''Missing From DB: ' + @UpgradedDB + ''' AS Descr
from ' + @UpgradedDB + '.sys.objects obj
join ' + @UpgradedDB + '.sys.indexes ind on obj.object_id = ind.object_id
join ' + @UpgradedDB + '.sys.columns col on obj.object_id = col.object_id
join ' + @UpgradedDB + '.sys.sysindexkeys keys on (keys.id = col.object_id AND keys.colid = col.column_id) AND (ind.object_id = keys.id AND ind.index_id = keys.indid)
)
UNION ALL 
(
select obj.name as TableName
	,ind.name as IndexName
	,ind.type_desc as Clustering
	,col.name as ColName
	,keys.keyno as ColPosition
	,''Missing From DB: ' + @NetNewDB + ''' AS Descr
from ' + @UpgradedDB + '.sys.objects obj
join ' + @UpgradedDB + '.sys.indexes ind on obj.object_id = ind.object_id
join ' + @UpgradedDB + '.sys.columns col on obj.object_id = col.object_id
join ' + @UpgradedDB + '.sys.sysindexkeys keys on (keys.id = col.object_id AND keys.colid = col.column_id) AND (ind.object_id = keys.id AND ind.index_id = keys.indid)
EXCEPT 
select obj.name as TableName
	,ind.name as IndexName
	,ind.type_desc as Clustering
	,col.name as ColName
	,keys.keyno as ColPosition
	,''Missing From DB: ' + @NetNewDB + ''' AS Descr
from ' + @NetNewDB + '.sys.objects obj
join ' + @NetNewDB + '.sys.indexes ind on obj.object_id = ind.object_id
join ' + @NetNewDB + '.sys.columns col on obj.object_id = col.object_id
join ' + @NetNewDB + '.sys.sysindexkeys keys on (keys.id = col.object_id AND keys.colid = col.column_id) AND (ind.object_id = keys.id AND ind.index_id = keys.indid)
)
ORDER BY TableName, IndexName, ColPosition'

EXEC sp_executesql @SQL


	
	SET NOCOUNT OFF
END

GO

